Hierarchy functions

Hierarchy function is:

 

·          MDX function that is always applied to all elements from single dimension within set on a single axis (rows or columns),

 

List of functions that are supported in designer:

 

CubePlayer Functions	MDX Functions used to create CubePlayer Functions
Best N	Equivalent to TopCount
Best in Sum	Equivalent to TopSum
Best in Percent	Equivalent to TopPercent
Worst N	Equivalent to BottomCount
Worst in Sum	Equivalent to BottomSun
Worst in Percent	Equivalent to BottomPercent
Order	Equivalent to Order
Order by Alphabet	Combination of Order function and order conditions
Order by Hierarchy	Equivalent to Hierachize
Filter	Equivalent to Filter
Remove member(s)	Combination of Filter (and NOT members …)
First N	Equivalent to Head
Last N	Equivalent to Tail
Without empties	Equivalent to NonEmpty
Show Visual Totals	Equivalent to VisualTotals
Parallel Time Members	Implementation of generate and ParallelPeriods
DrillD Down Level	Implementations of Generate to select level

 

 

On each axis, once hierarchy function is applied each member from selected hierarchy will have red box with white letters fn

 

This way you will always be notified about the function presence.

 

Hierarchy functions

Hierarchy functions will always be applied around one dimension/hierarchy.

 

Example                   We have level State Province on Columns

We will apply TopCount 100 on entire axis

 

SELECT

NON EMPTY

TopCount

    (

        {

        [Customer].[hCountry].[State Province].MEMBERS

        }

        ,100, [Measures].[Store Sales]

    )

ON AXIS(0)

FROM

[Sales]

 

At this point Axis function and Dimension function are the same since they are applied on only one dimension.

 

Let us add another dimension/hierarchy Product Family

 

SELECT

NON EMPTY

CrossJoin

    (

    TopCount

        (

            {

            [Customer].[hCountry].[State Province].MEMBERS

            },

            100,

            [Measures].[Store Sales]

        ),

        {

        [Product].[hProduct].[Product Family].MEMBERS

        }

    )

ON AXIS(0)

FROM

[Sales]

 

As you can see TopCount is now inside CrossJoin applied only on dimension Customer.

Let us add another member or level from dimension Customer. In our case Member USA

 

SELECT

NON EMPTY

CrossJoin

    (

    TopCount

        (

            {

            [Customer].[hCountry].[State Province].MEMBERS ,

            [Customer].[hCountry].[Country].&[USA]

            },

            100,

            [Measures].[Store Sales]

        ),

        {

        [Product].[hProduct].[Product Family].MEMBERS

        }

    )

ON AXIS(0)

FROM

[Sales]

 

Since TopCount is applied to dimension Customer, member from dimension Customer 

is placed inside TopCount function, therefore TopCount function will have influence to

that member as well.

 

 

 

When Hierarchy function is applied, the elements belonging to that Hierarchy will show a red flag fn to notify you of it's presence (Figure 6-7).

 

Figure 6-6: Hierarchy function is applied to the elements belonging to hierarchy hCountry. Product Family that belongs to the hierarchy hProduct is not affected with it.

 

Hierarchy functions will always be applied to one dimension/hierarchy.

Example: We have level State Province on Columns. We will apply hierarchy function TopCount 100.

SELECT 
NON EMPTY 
TopCount 
( 
{ 
[Customer].[hCountry].[State Province].AllMembers 
} 
, 100, ([Measures].[Store Sales]) 
) 
ON 0 
FROM 
[Sales]

 

At this point, Axis function and Hierarchy function are the same since they are applied on only one dimension.

Let us add level from another dimension/hierarchy, Product Family.

SELECT 
NON EMPTY 
CrossJoin 
( 
TopCount 
( 
{ 
[Customer].[hCountry].[State Province].AllMembers 
} 
, 100, ([Measures].[Store Sales]) 
) 
, 
{ 
[Product].[hProduct].[Product Family].AllMembers 
} 
) 
ON 0 
FROM 
[Sales]

As you can see, TopCount is now inside CrossJoin applied only on dimension Customer.Let us add another member or level from dimension Customer. In our case Member USA.

SELECT 
NON EMPTY 
CrossJoin 
( 
TopCount 
( 
{ 
[Customer].[hCountry].[State Province].AllMembers 
,[Customer].[hCountry].[Country].&[USA] 
} 
, 100, ([Measures].[Store Sales]) 
) 
, 
{ 
[Product].[hProduct].[Product Family].AllMembers 
} 
) 
ON 0 
FROM 
[Sales]

Since TopCount is applied to dimension Customer, member from dimension Customer is placed inside TopCount function, therefore TopCount function will have influence to that member as well.

 

Applying the Hierarchy (Dimension) functions

1.     Fill row or column data area with cube elements

2.     Right-click the element belonging to the hierarchy you wish to apply function to

3.     Select Hierarchy Functions, and then select function from the list

Levels City and Product Family are added to the Rows. We will apply Hierarchy function to the level City.

4.     Hierarchy Function dialog will appear.

 

The dialog  for the hierarchy function Best N

5.     Select Measure to apply the function from drop down list (a default measure will be predefined)

6.     Select Value (if applicable)

7.     Click Create

 

 

Editing and removing Hierarchy (Dimension) functions

Editing and removing hierarchy functions follows the same principle as the axis functions do, the difference being that the options Edit Function and Delete Function are located in the Hierarchy Functions submenu (Figure 6-10).

Figure 6-10: The options to edit or remove hierarchy functions